配列をネストしたJSONをJSON SerDeライブラリで読み込んでフラット化してみた

データアナリティクス事業本部の鈴木です。

今回は配列をネストしたJSONLファイルをJSON SerDeライブラリで読み込み、フラット化して分析に使用したいことがあったので、試してみました。

やりたいこと

以下のようなJSONLファイルを、

{"col1":"a", "col2":{"col_21":["a1","a2","a3","a4"], "col_22":["a5","a6","a7","a8"]}}
{"col1":"b", "col2":{"col_21":["b1","b2","b3","b4"], "col_22":["b5","b6","b7","b8"]}}
{"col1":"c", "col2":{"col_21":["c1","c2","c3","c4"], "col_22":["c5","c6","c7","c8"]}}

以下のようにAthenaでフラット化して検索することが目標です。

このようにすることで、JSON内にネストしている配列のデータを使って、ほかのテーブルと結合し、分析を進められるケースがあります。

ポイント

今回、やりたいことを実現するために、以下の２つの技術要素がポイントになったので、ご紹介します。

1. JSON SerDe ライブラリ

Athenaでは、JSONを読み出すために、現状以下の2種類のSerDeライブラリがあります。

• Hive JSON SerDe
• OpenX JSON SerDe

今回はOpenX JSON SerDeを使ってJSONLファイルに検索を実行します。

{"id": 1, "profile": {"name": "sasaki", "age": 24}}
{"id": 2, "profile": {"name": "tanaka", "age": 36}}
{"id": 3, "profile": {"name": "suzuki", "age": 27}}

このJSONLファイルは、例えばs3://cm-nayuts-sample/unnest_sample1/にアップロードしておくとします。

続いて、以下のDDLを実行し、sample_json_table1テーブルを作成します。

オブジェクトのデータに対応するカラムは、以下のようにstructもしくはmapで定義する必要があります。

CREATE EXTERNAL TABLE `sample_json_table1`(
    id int 
  , profile struct<name:string, age:int>
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
LOCATION
  's3://cm-nayuts-sample/unnest_sample1/'

これでJSONLをAthenaで読み出す準備が完了です。.でJSONのバリューにアクセスすることができるので、これを使ってファイルを検索してみます。

SELECT
  id
  profile.name
  profile.age
FROM sample_json_table1

JSONLファイルからデータを読み出してを構造化することができました。

2. UNNEST

UNNESTを使うことで、ARRAYやMAPを構造化したデータに展開することができます。 ARRAYだと１列、MAPだと２列に展開されます。

Athena エンジンバージョン2が基づいているPresto 0.217のドキュメントだと以下に記載があります。

8.26. SELECT — Presto 0.217 Documentation

まず簡単な例を確認します。

-- 以下のドキュメントの例を引用しました。
-- https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html
WITH dataset AS (
  SELECT
    'engineering' as department,
    ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users
)
SELECT department, names FROM dataset
CROSS JOIN UNNEST(users) as t(names)

複数の配列を持たせた場合は以下のようになりました。

-- 以下のドキュメントの例を改変しました。
-- https://docs.aws.amazon.com/athena/latest/ug/flattening-arrays.html
WITH dataset AS (
  SELECT
    'engineering' as department,
    ARRAY['Sharon', 'John', 'Bob', 'Sally'] as users,
    ARRAY[28, 25, 36] as ages
)
SELECT department, names , age FROM dataset
CROSS JOIN UNNEST(users, ages) as t(names, age)

agesカラムはわざと長さを短くしていていますが、足りない分はnullになることが分かります。

やってみる

ようやく本題ですが、先ほど見た２つのポイントを使って以下のJSONLファイルを読み込んでフラット化してみます。

{"col1":"a", "col2":{"col_21":["a1","a2","a3","a4"], "col_22":["a5","a6","a7","a8"]}}
{"col1":"b", "col2":{"col_21":["b1","b2","b3","b4"], "col_22":["b5","b6","b7","b8"]}}
{"col1":"c", "col2":{"col_21":["c1","c2","c3","c4"], "col_22":["c5","c6","c7","c8"]}}

まず検索用のsample_json_table2テーブルを定義します。JSONLファイルはあらかじめS3にアップされているとします。

CREATE EXTERNAL TABLE `sample_json_table2`(
  col1 string,
  col2 struct<col_21:array<string>, col_22:array<string>>
)
ROW FORMAT SERDE 
  'org.openx.data.jsonserde.JsonSerDe' 
LOCATION
  's3://cm-nayuts-sample/unnest_sample2/'

.を使って、以下のように配列を1カラムに読み出すことができます。

SELECT
    col1,
    col2.col_21,
    col2.col_22
FROM sample_json_table2

この操作でUNNESTの解説で見た状態にすることができました。

続いて、col_21とcol_22をUNNESTを使うことでフラット化してみましょう。

SELECT
    col1,
    col_21,
    col_22
FROM sample_json_table2
CROSS JOIN UNNEST(col2.col_21, col2.col_22) as t(col_21, col_22)

期待通りに構造化することができました。

最後に

今回はネストしたJSONLファイルをJSON SerDeライブラリで読み込んでフラット化する例をご紹介しました。

どこかで誰かのお役に立てば幸いです。

参考

• Amazon Athenaを使ってJSONファイルを検索してみる | DevelopersIO
• JSON SerDe ライブラリ - Amazon Athena